---
description: Hasura Cloud and Hasura Enterprise dynamic database connection routing
title: 'Cloud and Enterprise Edition: Dynamic database connection routing'
keywords:
  - hasura
  - docs
  - cloud
  - enterprise
  - dynamic databases
  - dynamic connections
  - dynamic database connections
  - dynamic database connection routing
  - connection routing
  - routing
  - connections
  - pool
sidebar_label: Dynamic Routing for Databases
sidebar_position: 3
sidebar_class_name: cloud-and-enterprise-icon
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Thumbnail from '@site/src/components/Thumbnail';
import HeadingIcon from '@site/src/components/HeadingIcon';
import ProductBadge from '@site/src/components/ProductBadge';

# Dynamic Routing for Databases

<ProductBadge pro ee self />

## Introduction

Hasura Cloud and Enterprise customers can leverage dynamic routing to implement different kinds of database topology
patterns with Hasura. You can group different databases using a new Metadata configuration object called
'connection-set' and leverage the [Kriti template](/api-reference/kriti-templating.mdx) to define custom 'connection
templates' to route GraphQL requests based on different request parameters such as session variables, headers and tenant
IDs.

:::info Caution

- Dynamic Database Connection Routing is available only for Postgres and Postgres-like backends currently.

:::

Following are some of the top use cases for this feature:

### Connect with different database credentials

If you are integrating with a vendor IAM or wish to use database RLS, you will want to use different connection
credentials per request. For example, in Hasura, you may use a session variable like `x-hasura-role` and use a specific
connection for that role.

### No-stale reads when read replicas are configured

If read replicas are configured for a given source, then all query operations are routed to the read replicas, which
sometimes leads to stale reads because of replication lag. You can force certain query operations to the primary
connection by using some operation context variable like an operation name or a special request header.

### Route to a specific shard or node group in a distributed database

In a distributed database system like YugabyteDB or CockroachDB, sometimes, you may want to route the request to a
specific node. You can achieve this in Hasura using a connection template.

## How it works

When a request is executed, dynamic routing will use a connection template (in Kriti lang) to resolve a connection from
a connection set. Note that only non-admin roles use the connection template. The admin role will always use the
`primary` connection.

There are two configurable parameters in the source configuration useful for dynamic routing:

1. [Connection set](#connection-set)
2. [Connection template](#connection-template)

### Connection set

You can define a set of connections that are available for routing. You can refer the members of this connection set in
the [connection template](#connection-template) by their unique name using the variable
`$.connection_set.<member's name>`.

### Connection template

The logic for connection routing is defined as a [Kriti template](/api-reference/kriti-templating.mdx). This can resolve
to the following:

1. **A member of the connection set**

   The connection will be routed to a member of the connection set. **The request will fail if the name is not defined
   in the connection set**. To use this, the template should resolve to the following variable:

   ```
   $.connection_set.<name of a member of the connection set>
   ```

2. **Predefined connections**

   The query can also be routed to connections that are already defined:

   1. **Primary source**

   The query will be routed to the connection specified under `connection_info`. To use this, the template should
   resolve to the following variable: ` $.primary`

   2. **Read replicas**

   The query will be routed to a randomly chosen member from the set of read replicas. **Please note that for mutations,
   this will fail.** To use this, the template should resolve to the following variable: ` $.read_replicas`

   3. **Default**

   The connection template resolving to the `$.default` variable directs the query to follow the default behavior. E.g.,
   if read replicas are configured, then all queries and subscriptions are routed to read replicas and mutations are
   routed to the primary connection. Otherwise, Hasura will execute all GraphQL queries on the primary connection.

The context for the connection template contains the request variables (`$.request`). The request variable includes the
following:

1. **HTTP headers**

   These are client headers associated with the request. Users can refer to the headers in the template using the
   variable `$.request.headers`. Header names are case insensitive; hence their names are provided in lowercase in the
   template context. An example template using HTTP headers:

   ```
   {{ if ($.request.headers?[no-stale-read] == "true")}}
     {{$.primary}}
   {{ else }}
     {{$.read_replicas}}
   ```

   For this example, if the header `no-stale-read` is set to `true`, then Hasura will route the requests to the
   `primary` source. Otherwise, it will use the `read_replicas`.

2. **Session variables**

   These are the key-value pairs returned from your authentication service. They can be referred to in the template
   using the variable `$.request.session`. The session variable keys always contain `x-hasura-*` as the prefix. An
   example template using session variables:

   ```
   {{ if $.request.session?["x-hasura-role"] == "dev" }}
     {{ $.​connection_set.dev_db }}
   {{ else }}
     {​{ if ​$.connection_set?[$.request.session.x-hasura-tenant-id] != null }}
       {{ ​$.connection_set[$.request.session.x-hasura-tenant-id] }}
     {{ else }}
       {{ $.default }}
     {{ end }}
   {{ end }}
   ```

   For this example, if the session variable `x-hasura-role` is set to `dev`, then Hasura will route the requests to the
   `dev_db` in connection_set. Otherwise, Hasura will look up if there is a connection member with the name of
   `x-hasura-tenant-id` and route to that member if it exists. If not, then it will fall back to the default behavior.

3. **Graphql query parameters**

   These are the operation type and operation name for the GraphQL query. They can be referred to in the template using
   the variable `$.request.query`. This variable can only have two keys:
   [operation type](https://spec.graphql.org/October2021/#OperationType) and
   [operation name](https://spec.graphql.org/October2021/#sec-Named-Operation-Definitions) (optional). An example
   template using the query parameters:

   ```
   {{ if ($.request.query.operation_type == "mutation") }}
       {{$.primary}}
   {{ else }}
       {{$.read_replicas}}
   {{ end }}
   ```

   For this example, Hasura will route to the `primary` database for mutations and `read_replicas` for everything else.

An example of the request context:

```json
{
  "headers": {
    "x-hasura-role": "user",
    ...
  },
  "session": {
    "x-hasura-role": "user",
    "x-hasura-org": "hasura",
    ...
  },
  "query": {
    "operation_type": "query",
    "operation_name": "MyQuery"
  }
}
```

You can build your connection template using these variables. An example of a connection template is given below:

Example:

```
{{ if ($.request.query.operation_type == "query") || ($.request.query.operation_type == "subscription") }}
  {{ if $.request.session?["x-hasura-role"] == "developer" }}
      {{if $.request.headers?["route-to-read-replicas"] == "true"}}
        {{$.read_replicas}}
      {{else}}
        {{$.connection_set.dev_db}}
      {{end}}
  {{else}}
      {{$.default}}
  {{ end }}
{{else}}
    {{ if $.request.session?["x-hasura-role"] == "priority-user" }}
        {{$.connection_set.fast_db}}
    {{else}}
        {{$.primary}}
    {{ end }}
{{ end }}
```

Explanation:

- For query/subscription GraphQL operations, if the `x-hasura-role` is `developer`, then use `read_replicas` if the
  header `route-to-read-replicas` is set to `true`. Otherwise, use the `dev_db` from the connection set.
- For query/subscription GraphQL operations, if the `x-hasura-role` is not `developer`, then route using the `default`
  behavior.
- For mutations, if the `x-hasura-role` is set to `priority-user`, then use the `fast_db` from the connection set; else,
  use the `primary` connection.

## Setting up connection set and connection template

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

To access Dynamic Routing, navigate to the `Dynamic Routing` tab in the Edit Data Source page.

Select the template you want to use from the list of available templates, or click on `Custom Template` to create your
own template.

<Thumbnail
  src="/img/databases/postgres/dynamic-db-routing/edit-connection-template.png"
  alt="Dynamic routing"
  max-width="543px"
/>

To add a new database connection, click on the `Add Connection` button. Enter the necessary connection details in the
modal that opens up.

You can also edit or delete an existing connection by clicking on the `Edit Connection` or `Remove` button next to the
connection.

<Thumbnail
  src="/img/databases/postgres/dynamic-db-routing/add-connection.png"
  alt="Dynamic routing"
  max-width="543px"
/>

Adjust the connection template settings to suit your needs, then click `Update Connection Template` to save the changes.

</TabItem>
<TabItem value="cli" label="CLI">

You can add _connection set_ and _connection template_ for a database by adding their config to the
`/metadata/databases/database.yaml` file:

```yaml {11-22}
- name: <db-name>
  kind: postgres
  configuration:
    connection_info:
      database_url:
        from_env: <DATABASE_URL_ENV>
      pool_settings:
        idle_timeout: 180
        max_connections: 50
        retries: 1
    connection_template:
      template: |
        {{ if $.request.session?["x-hasura-role"] == "user" }}
          {{$.primary}}
        {{else}}
          {{$.connection_set.db_1}}
        {{ end }}
    connection_set:
      - name: db_1
        connection_info:
          database_url:
            from_env: <DATABASE_URL_ENV>
```

Apply the Metadata by running:

```yaml
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

The _connection set_ and _connection template_ can be configured via the
[pg_add_source](/api-reference/metadata-api/source.mdx#metadata-pg-add-source) Metadata API.

```http {20-32}
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_add_source",
  "args": {
    "name": "db",
    "configuration": {
      "connection_info": {
        "database_url": {
          "from_env": "<DATABASE_URL_ENV>"
        },
        "pool_settings": {
          "idle_timeout": 180,
          "max_connections": 50,
          "retries": 1
        }
      },
      "connection_template": {
        "template": "{{ if $.request.session?[\"x-hasura-role\"] == \"user\" }} {{$.primary}} {{else}} {{$.connection_set.db_1}} {{ end }}"
      },
      "connection_set": [
        {
          "name": "db_1",
          "connection_info": {
            "database_url": {
              "from_env": "<DATABASE_URL_ENV>"
            }
          }
        }
      ]
    }
  }
}
```

</TabItem>
</Tabs>

## Testing connection template

Hasura offers a convenient way to test the connection template for a source through the Hasura Console, which simulates
an actual GraphQL request without hitting the database. The Hasura Console internally uses the Metadata API
([pg_test_connection_template](/api-reference/metadata-api/table-view.mdx#metadata-pg-test-connection-template)) for
this process. Alternatively, you can also test the connection template directly using the Metadata API.

<Tabs groupId="testing-connection-template" className="api-tabs">
<TabItem value="console" label="Console">

1. Navigate to the `Dynamic Routing` tab in the Edit Data Source page.
2. Click on `Validate` button next to the connection template you want to test.

<Thumbnail
  src="/img/databases/postgres/dynamic-db-routing/validate-connection-template.png"
  alt="Dynamic DB routing"
  max-width="543px"
/>

3. Fill in the `Headers`, `Session Variables`, `Operation Type and Name` as needed for your test scenario.
4. Click the `Validate` button and the result will be displayed.
5. You can edit the connection template in the validate modal, the modified template will be updated in the previous
   screen.

<Thumbnail
  src="/img/databases/postgres/dynamic-db-routing/validate-connection-template-modal.png"
  alt="Dynamic DB routing"
  max-width="543px"
/>

</TabItem>

<TabItem value="api" label="API">

Request:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_test_connection_template",
  "args": {
    "source_name": "source_name",
    "request_context": {
      "headers": {
        "header_name": "header_value"
      },
      "session": {
        "session_var": "session_var_value"
      },
      "query": {
        "operation_type": "query",
        "operation_name": "op_name"
      }
    },
    "connection_template": {
        "template": "{{ if $.request.session?[\"x-hasura-role\"] == \"user\" }} {{$.primary}} {{else}} {{$.connection_set.db_1}} {{ end }}"
      }
  }
}
```

Success Response:

```json
{
  "result": {
    "routing_to": "connection_set",
    "value": "connection_set_member_name"
  }
}
```

:::info Note

`connection_template` is an optional argument which has precedence over the connection template present in the source.
If `connection_template` is provided in the API, then the source's connection template will be ignored.

:::

</TabItem>
</Tabs>

## Limitations

### Postgres schema of connection set

Hasura derives the GraphQL schema based on the primary connection only (i.e., `connection_info`). The Postgres schema of
all members of `connection_set` should be identical to that of the primary connection. **Hasura does not make any checks
to ensure the Postgres schema consistency, and users should guarantee the same.** Also, you can only configure/update
the primary database on the Hasura Console. Other databases are not accessible via the Console.

:::info Caution

A GraphQL request may result in a runtime exception when it is being executed on a member of the connection set that
differs in the Postgres schema from the primary connection.

:::

### Event Triggers

[Hasura Event Triggers](/event-triggers/overview.mdx) are triggered only for mutations executed on the primary
connection. Mutations routed to the members of the connection set will not trigger Event Triggers.

### Migrations

Hasura [CLI Migrations](/migrations-metadata-seeds/manage-migrations.mdx) cannot be applied on a connection set member.
